<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace App;
class MySQLPDODatabase extends PDODatabase {

    protected static $sql_var = array(
        'current_date_utc'       => "utc_date()",
        'current_date_local'     => "curdate()",
        'current_datetime_utc'   => "utc_timestamp()",
        'current_datetime_local' => "now()"
    );

    protected function _dsn () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->dsn)) {
            $dsn = $this->dsn;
        }
        elseif (isset($this->dbtype)) {
            if (isset($this->schema_name)) {
               if (isset($this->dbhost)) {
                    $dsn = "mysql:host=$this->dbhost;dbname=$this->schema_name;charset=utf8";
                }
                else {
                    $dsn = "mysql:dbname=$this->schema_name;charset=utf8";  
                }
            }
        }
        if (!isset($dsn)) {
            throw new \Exception("Could not determine the appropriate DSN for the requested database ($this->name).");
        }
        if ($trace) trace_exit($dsn);
        return($dsn);
    }

    protected function _dbattr () {
        global $trace;
        if ($trace) trace_entry();
        $dbattr = array(
            \PDO::MYSQL_ATTR_LOCAL_INFILE => true,             # Enable LOAD LOCAL INFILE (enable import from local file)
            \PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8', # Command to execute on connect/reconnect. (set charset to UTF8. unnecessary now, but for backward compatibility.)
            \PDO::MYSQL_ATTR_FOUND_ROWS   => true              # Return the number of found (matched) rows, not the number of changed rows.
        );
        if ($trace) trace_exit($dbattr);
        return($dbattr);
    }

    protected function _get_schema_defs_from_db () {
        global $trace;
        if ($trace) trace_entry();
        $sql = "select schema_name, 'Y' as physical_ind from information_schema.schemata where schema_name = '$this->schema_name' order by schema_name";
        $schema_defs = $this->get_hash_of_hashes_by_key($sql, null, null, 'schema_name');
        if ($trace) trace_exit($schema_defs);
        return($schema_defs);
    }

    protected function _get_table_defs_from_db ($schema) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema)) $schema = $this->schema_name;
        $sql = "select t.table_name from information_schema.tables t where t.table_schema = '$schema' and (t.table_type like '%TABLE%' or t.table_type like '%VIEW%') order by t.table_name";
        $rows = $this->get_rows($sql);
        $table_defs = Array();
        foreach ($rows as $row) {
            $table_name = strtolower($row[0]);
            $table_label = $this->make_label($table_name);
            $table_defs[$table_name] = Array('table_name' => $table_name, 'table_label' => $table_label);
        }
        if ($trace) trace_exit($table_defs);
        return($table_defs);
    }

    public function get_table_def_from_db ($table, $schema) {
        global $trace;
        if ($trace) trace_entry();
        $table_def     = $this->get_hash("select table_name, table_rows as est_row_count, data_length as est_data_size, index_length as est_index_size from information_schema.tables where table_schema = '$schema' and table_name = '$table'");
        if ($table_def) {
            $column_defs   = $this->_get_column_defs_from_db($schema, $table);
            if (count($column_defs) >= 1) {
                $phys_columns  = array_keys($column_defs);
                $table_def['schema_name']  = $schema;
                $table_def['db_name']      = $this->db_name;
                $table_def['physical_ind'] = 'Y';
                $table_def['column']       = $column_defs;
                $table_def['columns']      = $phys_columns;
                $table_def['phys_columns'] = $phys_columns;
                $this->_get_index_defs_from_db($schema, $table, $table_def);
            }
            else {
                $table_def = null;
            }
        }
        if ($trace) trace_exit($table_def);
        return($table_def);
    }

    protected function _get_column_defs_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema)) $schema = $this->schema_name;
        $sql = "select c.column_name, c.column_type as native_column_type, c.ordinal_position as order_idx, c.column_default as default_value, (case when c.is_nullable = 'NO' then 'Y' else 'N' end) as not_null_ind, coalesce(c.character_maximum_length,c.numeric_precision) as max_length, (case c.column_key when 'PRI' then 'Y' else 'N' end) as primary_key_ind, 'Y' as physical_ind, (case when c.extra like '%auto_increment%' then 'Y' else 'N' end) as auto_increment_ind from information_schema.columns c where c.table_schema = '$schema' and c.table_name = '$table' order by c.ordinal_position";
        $column_defs = $this->get_hash_of_hashes_by_key($sql, null, null, 'column_name');
        foreach ($column_defs as $column_name => $column_def) {
            $column_defs[$column_name]['column_type'] = $this->native_type_to_app_type($column_def['native_column_type']);
        }
        if ($trace) trace_exit($column_defs);
        return($column_defs);
    }

# mysql> show keys from dbfoun_dev_1_prod_app.person;
# +--------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
# | Table  | Non_unique | Key_name   | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
# +--------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
# | person |          0 | PRIMARY    |            1 | person_id      | A         |        2977 |     NULL | NULL   |      | BTREE      |         |               |
# | person |          0 | person_ie2 |            1 | owner_group_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
# | person |          0 | person_ie2 |            2 | email          | A         |        2977 |     NULL | NULL   | YES  | BTREE      |         |               |
# | person |          1 | person_ie1 |            1 | owner_group_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
# | person |          1 | person_ie1 |            2 | first_name     | A         |        2977 |     NULL | NULL   | YES  | BTREE      |         |               |
# +--------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
# mysql> select index_name, (case when non_unique = 1 then 'N' else 'Y' end) as unique_ind, column_name,  from statistics where table_schema = 'dbfoun_dev_1_prod_app' and table_name = 'person';
# +---------------+-----------------------+------------+------------+-----------------------+------------+--------------+----------------+-----------+-------------+
# | TABLE_CATALOG | TABLE_SCHEMA          | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA          | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME    | COLLATION | CARDINALITY |
# +---------------+-----------------------+------------+------------+-----------------------+------------+--------------+----------------+-----------+-------------+
# | def           | dbfoun_dev_1_prod_app | person     |          0 | dbfoun_dev_1_prod_app | PRIMARY    |            1 | person_id      | A         |        2977 |
# | def           | dbfoun_dev_1_prod_app | person     |          0 | dbfoun_dev_1_prod_app | person_ie2 |            1 | owner_group_id | A         |           2 |
# | def           | dbfoun_dev_1_prod_app | person     |          0 | dbfoun_dev_1_prod_app | person_ie2 |            2 | email          | A         |        2977 |
# | def           | dbfoun_dev_1_prod_app | person     |          1 | dbfoun_dev_1_prod_app | person_ie1 |            1 | owner_group_id | A         |           2 |
# | def           | dbfoun_dev_1_prod_app | person     |          1 | dbfoun_dev_1_prod_app | person_ie1 |            2 | first_name     | A         |        2977 |
# +---------------+-----------------------+------------+------------+-----------------------+------------+--------------+----------------+-----------+-------------+
# +----------+--------+----------+------------+---------+---------------+
# | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT |
# +----------+--------+----------+------------+---------+---------------+
# |     NULL | NULL   |          | BTREE      |         |               |
# |     NULL | NULL   |          | BTREE      |         |               |
# |     NULL | NULL   | YES      | BTREE      |         |               |
# |     NULL | NULL   |          | BTREE      |         |               |
# |     NULL | NULL   | YES      | BTREE      |         |               |
# +----------+--------+----------+------------+---------+---------------+
# mysql> use dbfoun_dev_1_prod_app;
# mysql> show table status like 'person';
# +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+
# | Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         |
# +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+
# | person | InnoDB |      10 | Compact    | 2977 |            533 |     1589248 |               0 |       262144 |   4194304 |           3001 | 2014-02-21 03:45:14 |
# +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+
# +-------------+------------+-----------------+----------+----------------+---------+
# | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
# +-------------+------------+-----------------+----------+----------------+---------+
# | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
# +-------------+------------+-----------------+----------+----------------+---------+
# mysql> select * from tables where table_schema = 'acoexh_dev_1_prod_app' and table_name = 'person';
# +---------------+-----------------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+
# | TABLE_CATALOG | TABLE_SCHEMA          | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH |
# +---------------+-----------------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+
# | def           | acoexh_dev_1_prod_app | person     | BASE TABLE | InnoDB |      10 | Compact    |       2977 |            533 |     1589248 |               0 |
# +---------------+-----------------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+
# +--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
# | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
# +--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
# |       262144 |   4194304 |           3001 | 2014-01-30 17:09:23 | NULL        | NULL       | utf8_general_ci |     NULL |                |               |
# +--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+

    protected function _get_index_defs_from_db ($schema, $table, &$table_def) {
        global $trace;
        if ($trace) trace_entry();

        $sql = "select index_name, (case when non_unique = 1 then 'N' else 'Y' end) as unique_ind, column_name, seq_in_index as order_idx from information_schema.statistics where table_schema = '$schema' and table_name = '$table'";
        $index_column_defs     = $this->get_rows($sql);
        $primary_key           = null;
        $auto_increment_column = null;
        $unique_indexes        = array();
        $indexes               = array();
        $index_defs            = array();
        $last_index_name       = '';
        $index_order_idx       = 0;
        foreach ($index_column_defs as $index_column_def) {
            list($index_name, $unique_ind, $column_name, $order_idx) = $index_column_def;
            $primary_key_ind    = ($index_name === 'PRIMARY') ? 'Y' : 'N';
            if (isset($table_def['column'][$column_name]) &&
                isset($table_def['column'][$column_name]['auto_increment_ind']) &&
                      $table_def['column'][$column_name]['auto_increment_ind'] === 'Y') {
                $auto_increment_ind = 'Y';
                $auto_increment_column = $column_name;
            }
            else {
                $auto_increment_ind = 'N';
            }
            if ($index_name !== $last_index_name) {
                if ($last_index_name) {
                    if     ($index_def['primary_key_ind'] === 'Y') { $primary_key      = $index_def['columns']; }
                    elseif ($index_def['unique_ind'] === 'Y')      { $unique_indexes[] = $index_def['columns']; }
                    else                                           { $indexes[]        = $index_def['columns']; }
                    $index_defs[] = $index_def;
                }
                $index_order_idx++;
                $index_def = array(
                    'index_name' =>         $index_name,
                    'primary_key_ind' =>    $primary_key_ind,
                    'unique_ind' =>         $unique_ind,
                    'auto_increment_ind' => $auto_increment_ind,
                    'order_idx' =>          $index_order_idx,
                    'columns' =>            array()
                );
            }
            $index_def['columns'][] = $column_name;
            $last_index_name        = $index_name;
        }
        if ($last_index_name) {
            if     ($index_def['primary_key_ind'] === 'Y') { $primary_key      = $index_def['columns']; }
            elseif ($index_def['unique_ind'] === 'Y')      { $unique_indexes[] = $index_def['columns']; }
            else                                           { $indexes[]        = $index_def['columns']; }
            $index_defs[] = $index_def;
        }
        if ($auto_increment_column) $table_def['auto_increment_column'] = $auto_increment_column;
        $table_def['primary_key']    = $primary_key;
        $table_def['unique_indexes'] = $unique_indexes;
        $table_def['indexes']        = $indexes;
        $table_def['index_defs']     = $index_defs;

        if ($trace) trace_exit();
    }

    protected function _get_index_columns_from_db ($schema, $table, $index_defs, $column_defs) {
        global $trace;
        if ($trace) trace_entry();
        $all_index_rows = Array();
        #foreach ($index_defs as $index_name => $index_def) {
        #    $index_rows = $this->get_rows("pragma table_info('$index_name')");
        #    foreach ($index_rows as $index_row) {
        #        $row = Array();
        #        $row['order_idx']       = $index_row[0]+1;
        #        $row['index_id']        = $index_def['index_id'];
        #        $row['column_id']       = $column_defs[$index_row[2]]['column_id'];
        #        $all_index_rows[]       = $row;
        #    }
        #}
        if ($trace) trace_exit($all_index_rows);
        return($all_index_rows);
    }

    protected function _get_relationships_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        #$rows = $this->get_rows("pragma foreign_key('$table')");    # cid|name|type|notnull|dflt_value|pk
        $relationships = Array();
        #foreach ($rows as $row) {
        #    $relationship = Array();
        #    $relationship['order_idx']       = $row[0]+1;
        #    $relationship['column_name']     = strtolower($row[1]);
        #    $relationship['column_type']     = $this->native_type_to_app_type(strtolower($row[2]));
        #    $relationship['max_length']      = $row[3] ? $row[3] : null;
        #    $relationship['default_value']   = $row[4] ? $row[4] : null;
        #    $relationship['primary_key_ind'] = $row[5] ? 'Y' : 'N';
        #    $relationship['physical_ind'] = 'Y';
        #    $relationships[$relationship['column_name']] = $relationship;
        #}
        if ($trace) trace_exit($relationships);
        return($relationships);
    }

    protected function _get_schema_names () {
        global $trace;
        if ($trace) trace_entry();
        $sql = "select schema_name from information_schema.schemata order by schema_name";
        $schemas = $this->get_column($sql);
        if ($trace) trace_exit($schemas);
        return($schemas);
    }

    protected function _get_table_names ($schema_name = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema_name))
            $schema_name = $this->schema_name;
        $sql = "select table_name\nfrom information_schema.tables\nwhere table_schema = '$schema_name'\norder by table_name";
        $tables = $this->get_column($sql);
        if ($trace) trace_exit($tables);
        return($tables);
    }

    protected function _get_native_column_defs ($table, $schema_name = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema_name))
            $schema_name = $this->schema_name;
        $sql = "select column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length,\n       numeric_precision, numeric_scale, column_type, column_key, extra, privileges as max_length\nfrom information_schema.columns\nwhere table_schema = '$schema_name'\n  and table_name = '$table'\norder by ordinal_position";
        $column_defs = $this->get_hashes($sql);
        if ($trace) trace_exit($column_defs);
        return($column_defs);
    }

    /**
     * make_create_table_ddl($table_def, $ddls) - tbd
     * @param string  table_name         [IN] - the name of the table
     * @param string  table_label        [IN] - (optional) the label (text string) used to describe this table in the user interface (default is the table_name, converted to a label)
     * @param string  columns            [IN] - either a (csv) list of column_names or an array of column_names
     * @param string  phys_columns       [IN] - (optional) either a (csv) list of column_names or an array of column_names
     *                                          (if phys_columns exists, it overrides the columns value, which is instead interpreted to be a list of logical columns)
     * @param object  column             [IN] - (optional) an associated array of associative arrays with metadata about the columns
     *                                          e.g. array('person_id'=>array('column_name'=>'person_id','column_label'=>'Person ID','column_type'=>'integer','max_length'=>'10','not_null_ind'=>'Y',
                                                           'primary_key_ind'=>'Y','auto_increment_ind'=>'Y','default_value'=>null,'display_format'=>null,'display_justify'=>null))
     * @param array   primary_key        [IN] - (optional) an array of column_names which make up the primary key (if single column 'id' or ending in '_id', then it is an autoincrement column)
     * @param string  auto_increment_column [IN] - (optional) a column_name which is an autoincrement column
     * @param array   unique_indexes     [IN] - (optional) an array of arrays of column_names which make up alternate keys (uniqueness constraints with indexes)
     * @param array   indexes            [IN] - (optional) an array of arrays of column_names which make up non-unique indexes
     */
    public function make_create_table_ddl (&$table_def, &$ddls) {
        global $trace;
        if ($trace) trace_entry();

        $this->_make_canonical_table_def($table_def);
        $table_name   = $table_def['table_name'];
        $phys_columns = $table_def['phys_columns'];
        $column_defs  = $table_def['column'];
        $primary_key_column    = (isset($table_def['primary_key']) && count($table_def['primary_key']) == 1) ? $table_def['primary_key'][0] : '';
        $auto_increment_column = isset($table_def['auto_increment_column']) ? $table_def['auto_increment_column'] : '';
        $column_ddl_options = array();

        $ddl = "create table $this->schema_name.$table_def[table_name] (";
        foreach ($phys_columns as $i => $column_name) {
            if ($i > 0) $ddl .= ',';
            $column_ddl  = $this->make_table_column_ddl_fragment($column_name, $column_defs[$column_name],
                                                                 $auto_increment_column, $primary_key_column, $column_ddl_options);
            $ddl .= "\n    " . $column_ddl;
        }

        $primary_key_defined_in_column = (isset($column_ddl_options['primary_key_defined_in_column']) &&
                                                $column_ddl_options['primary_key_defined_in_column']) ? 1 : 0;

        if (isset($table_def['primary_key']) && count($table_def['primary_key']) >= 1 && !$primary_key_defined_in_column) {
            $ddl .= ",\n    primary key (" . implode(', ', $table_def['primary_key']) . ")";
        }

        $unique_index_count = 0;
        if (isset($table_def['unique_indexes'])) {
            foreach ($table_def['unique_indexes'] as $i => $index) {
                $unique_index_count = $i+1;
                if (count($index) >= 1) {
                    $ddl .= ",\n    unique index {$table_name}_ak{$unique_index_count} (" . implode(', ', $index) . ")";
                }
            }
        }
        $index_count = 0;
        if (isset($table_def['indexes'])) {
            foreach ($table_def['indexes'] as $i => $index) {
                $index_count = $i+1;
                if (count($index) >= 1) {
                    $ddl .= ",\n    index {$table_name}_ie{$index_count} (" . implode(', ', $index) . ")";
                }
            }
        }
        $ddl .= "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";
        $ddls[] = $ddl;

        if ($trace) trace_exit();
    }

    /**
     * make_alter_table_ddl(&$table_def, $table_def_from_db, &$ddls) - tbd
     * @param string  table_name         [IN] - the name of the table
     * @param string  table_label        [IN] - (optional) the label (text string) used to describe this table in the user interface (default is the table_name, converted to a label)
     * @param string  columns            [IN] - either a (csv) list of column_names or an array of column_names
     * @param string  phys_columns       [IN] - (optional) either a (csv) list of column_names or an array of column_names
     *                                          (if phys_columns exists, it overrides the columns value, which is instead interpreted to be a list of logical columns)
     * @param object  column             [IN] - (optional) an associated array of associative arrays with metadata about the columns
     *                                          e.g. array('person_id'=>array('column_name'=>'person_id','column_label'=>'Person ID','column_type'=>'integer','max_length'=>'10','not_null_ind'=>'Y',
                                                           'primary_key_ind'=>'Y','auto_increment_ind'=>'Y','default_value'=>null,'display_format'=>null,'display_justify'=>null))
     * @param array   primary_key        [IN] - (optional) an array of column_names which make up the primary key (if single column 'id' or ending in '_id', then it is an autoincrement column)
     * @param string  auto_increment_column [IN] - (optional) a column_name which is an autoincrement column
     * @param array   unique_indexes     [IN] - (optional) an array of arrays of column_names which make up alternate keys (uniqueness constraints with indexes)
     * @param array   indexes            [IN] - (optional) an array of arrays of column_names which make up non-unique indexes
     */
     # alter table foo
     #     drop primary key,
     #     add column steve integer,
     #     drop column grade,
     #     add index foo_ie1 (steve),
     #     add unique index foo_ak1 (age),
     #     add primary key (foo_id)
    public function make_alter_table_ddl(&$table_def, $table_def_from_db, &$ddls) {
        global $trace;
        if ($trace) trace_entry();

        $this->_make_canonical_table_def($table_def);

        $table_name            = $table_def['table_name'];
        $phys_columns          = $table_def['phys_columns'];
        $column_defs           = $table_def['column'];
        $primary_key_column    = (isset($table_def['primary_key']) && count($table_def['primary_key']) == 1) ? $table_def['primary_key'][0] : null;
        $current_primary_key_column = (isset($table_def_from_db['primary_key']) && count($table_def_from_db['primary_key']) == 1) ? $table_def_from_db['primary_key'][0] : null;
        $auto_increment_column = isset($table_def['auto_increment_column']) ? $table_def['auto_increment_column'] : null;
        $primary_key_defined_in_column = 0;

        $current_phys_columns  = $table_def_from_db['phys_columns'];
        $current_column_defs   = $table_def_from_db['column'];

        $alter_table_ddl       = "alter table $this->schema_name.$table_def[table_name]";

        $current_primary_key           = isset($table_def_from_db['primary_key'])           ? implode(', ',$table_def_from_db['primary_key']) : '';
        $primary_key                   = isset($table_def['primary_key'])                   ? implode(', ',$table_def['primary_key'])         : '';
        $current_auto_increment_column = isset($table_def_from_db['auto_increment_column']) ? $table_def_from_db['auto_increment_column']     : '';
        $auto_increment_column         = isset($table_def['auto_increment_column'])         ? $table_def['auto_increment_column']             : '';

        $num_changes = 0;

        # if primary key modified or dropped, drop primary key
        $primary_key_dropped = 0;
        if ($primary_key !== $current_primary_key || (!$primary_key && $current_primary_key)) {
            if (!$current_auto_increment_column) {
                if ($num_changes > 0) $alter_table_ddl .= ',';
                $alter_table_ddl .= "\n    drop primary key";
                $primary_key_dropped = 1;
                $num_changes++;
            }
        }
 
        # drop each index that is dropped or modified
        foreach ($table_def_from_db['index_defs'] as $index_def) {
            $index_name = $index_def['index_name'];
            if (isset($index_def['primary_key_ind']) && $index_def['primary_key_ind'] === 'Y') {
                # do nothing
            }
            elseif (preg_match("/^{$table_name}_(ie|ak)([0-9]+)$/", $index_name, $matches)) {
                $current_index_columns = implode(',', $index_def['columns']);
                $index_idx = $matches[2] - 1;
                if ($matches[1] === 'ie') {
                    $index_columns = (isset($table_def['indexes']) && count($table_def['indexes']) >= $index_idx+1)
                                   ? implode(',',$table_def['indexes'][$index_idx])
                                   : '';
                    if (!$index_columns || $index_columns !== $current_index_columns) {
                        if ($num_changes > 0) $alter_table_ddl .= ',';
                        $alter_table_ddl .= "\n    drop index $index_name";
                        $num_changes++;
                    }
                }
                elseif ($matches[1] === 'ak') {
                    $index_columns = (isset($table_def['unique_indexes']) && count($table_def['unique_indexes']) >= $index_idx+1)
                                   ? implode(',',$table_def['unique_indexes'][$index_idx])
                                   : '';
                    if (!$index_columns || $index_columns !== $current_index_columns) {
                        if ($num_changes > 0) $alter_table_ddl .= ',';
                        $alter_table_ddl .= "\n    drop index $index_name";
                        $num_changes++;
                    }
                }
            }
            else {
                if ($num_changes > 0) $alter_table_ddl .= ',';
                $alter_table_ddl .= "\n    drop index $index_name";
                $num_changes++;
            }
        }

        # modify all of the columns that need modifying
        $column_changed = array();
        foreach ($phys_columns as $i => $column_name) {
            $column_def          = $column_defs[$column_name];
            $column_ddl          = $this->make_table_column_ddl_fragment($column_name, $column_def, $auto_increment_column, $primary_key_column);
            $prev_column_name    = (isset($column_def['prev_column_name']) && $column_def['prev_column_name'] !== $column_name) ? $column_def['prev_column_name'] : '';

            if (isset($current_column_defs[$column_name]) &&
                isset($current_column_defs[$column_name]['physical_ind']) &&
                $current_column_defs[$column_name]['physical_ind'] === 'Y') {

                $current_column_def = $current_column_defs[$column_name];
                $current_column_ddl  = $this->make_table_column_ddl_fragment($column_name, $current_column_def, $current_auto_increment_column, $current_primary_key_column);
                if ($column_ddl !== $current_column_ddl) {
                    # if we are modifying a single-column primary key that was not yet dropped, then drop the primary key first
                    if ($column_name === $current_primary_key && !$primary_key_dropped) {
                        if ($num_changes > 0) $alter_table_ddl .= ',';
                        $alter_table_ddl .= "\n    drop primary key";
                        $primary_key_dropped = 1;
                        $num_changes++;
                    }
                    if ($num_changes > 0) $alter_table_ddl .= ',';
                    $alter_table_ddl .= "\n    modify column $column_ddl";
                    $alter_table_ddl .= ($i == 0) ? " first" : ' after '.$phys_columns[$i-1];
                    $num_changes++;
                }
            }
            elseif ($prev_column_name &&
                    isset($current_column_defs[$prev_column_name]['physical_ind']) &&
                    $current_column_defs[$prev_column_name]['physical_ind'] === 'Y') {

                # if we are modifying a single-column primary key that was not yet dropped, then drop the primary key first
                if ($prev_column_name === $current_primary_key && !$primary_key_dropped) {
                    if ($num_changes > 0) $alter_table_ddl .= ',';
                    $alter_table_ddl .= "\n    drop primary key";
                    $primary_key_dropped = 1;
                    $num_changes++;
                }
                if ($num_changes > 0) $alter_table_ddl .= ',';
                $alter_table_ddl .= "\n    change column $prev_column_name $column_ddl";
                $column_changed[$prev_column_name] = 1;
                $alter_table_ddl .= ($i == 0) ? " first" : ' after '.$phys_columns[$i-1];
                $num_changes++;
            }
            else {
                if ($num_changes > 0) $alter_table_ddl .= ',';
                $alter_table_ddl .= "\n    add column $column_ddl";
                $alter_table_ddl .= ($i == 0) ? " first" : ' after '.$phys_columns[$i-1];
                $num_changes++;
            }
        }
        foreach ($current_phys_columns as $i => $column_name) {
            if (!in_array($column_name, $phys_columns) && !isset($column_changed[$column_name])) {
                if ($num_changes > 0) $alter_table_ddl .= ',';
                $alter_table_ddl .= "\n    drop column $column_name";
                $num_changes++;
            }
        }

        # add each index that is added or modified
        if (isset($table_def['unique_indexes'])) {
            foreach ($table_def['unique_indexes'] as $i => $index_column_array) {
                $index_columns = implode(', ', $index_column_array);
                $current_index_columns = (isset($table_def_from_db['unique_indexes']) &&
                                          count($table_def_from_db['unique_indexes']) >= $i+1)
                                       ? implode(', ', $table_def_from_db['unique_indexes'][$i])
                                       : '';
                if ($index_columns !== $current_index_columns) {
                    $index_name    = $table_name . '_ak' . ($i+1);
                    if ($num_changes > 0) $alter_table_ddl .= ',';
                    $alter_table_ddl    .= "\n    add unique index $index_name ($index_columns)";
                    $num_changes++;
                }
            }
        }
        if (isset($table_def['indexes'])) {
            foreach ($table_def['indexes'] as $i => $index_column_array) {
                $index_columns = implode(', ', $index_column_array);
                $current_index_columns = (isset($table_def_from_db['indexes']) &&
                                          count($table_def_from_db['indexes']) >= $i+1)
                                       ? implode(', ', $table_def_from_db['indexes'][$i])
                                       : '';
                if ($index_columns !== $current_index_columns) {
                    $index_name    = $table_name . '_ie' . ($i+1);
                    if ($num_changes > 0) $alter_table_ddl .= ',';
                    $alter_table_ddl    .= "\n    add index $index_name ($index_columns)";
                    $num_changes++;
                }
            }
        }

        # if primary key modified or added, add primary key
        if ($primary_key !== $current_primary_key || ($primary_key && !$current_primary_key)) {
            if (!$auto_increment_column) {
                if ($num_changes > 0) $alter_table_ddl .= ',';
                $alter_table_ddl .= "\n    add primary key ($primary_key)";
                $num_changes++;
            }
        }

        if ($num_changes > 0) {
            $ddls[] = $alter_table_ddl;
        }

        if ($trace) trace_exit();
    }

    private function make_table_column_ddl_fragment ($column_name, $column_def,
                                                     $auto_increment_column = null, $primary_key_column = null, &$column_ddl_options = null) {
        global $trace;
        if ($trace) trace_entry();
        $column_type = $column_def['column_type'];
        $native_type = $this->app_type_to_native_type($column_type,
                                                      $column_def['max_length'],
                                                      isset($column_def['number_of_decimals']) ? $column_def['number_of_decimals'] : null,
                                                      $column_name);
        $null_clause = $column_def['not_null_ind'] === 'Y' ? 'not null' : 'null';
        $column_ddl = sprintf("%-20s %-12s %s", $column_name, $native_type, $null_clause);
        if (isset($column_def['default_value'])) {
            $default_value = $column_def['default_value'];
            if ($column_type === 'datetime') {
                if ($column_name === 'modify_dttm' || $default_value === 'CURRENT_TIMESTAMP_ON_UPDATE') {
                    $column_ddl .= ' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP';
                }
                elseif ($column_name === 'create_dttm' || $default_value === 'CURRENT_TIMESTAMP') {
                    $column_ddl .= ' DEFAULT CURRENT_TIMESTAMP';
                }
                elseif (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]$/', $default_value)) {
                    $column_ddl .= " DEFAULT '$default_value'";
                }
            }
            elseif ($column_type === 'integer' && preg_match('/^-?[0-9]+$/', $default_value)) {
                $column_ddl .= " DEFAULT $default_value";
            }
            elseif ($column_type === 'float' && preg_match('/^-?[0-9]+\\.?[0-9]*$/', $default_value)) {
                $column_ddl .= " DEFAULT $default_value";
            }
            elseif ($column_type === 'string' && preg_match('/^[^\'"]*$/', $default_value)) {
                $column_ddl .= " DEFAULT '$default_value'";
            }
        }
        $default_value = isset($column_def['default_value']) ? $column_def['default_value'] : null;
        if ($column_name === $auto_increment_column) {
            $column_ddl .= " AUTO_INCREMENT";
        }
        if ($column_name === $primary_key_column) {
            $column_ddl .= " PRIMARY KEY";
            if (isset($column_ddl_options)) $column_ddl_options['primary_key_defined_in_column'] = 1;
        }

        if ($trace) trace_exit($column_ddl);
        return($column_ddl);
    }

    /**
     * make_drop_table_ddl($table_name, $ddls) - tbd
     * @param string  table_name         [IN] - the name of the table
     */
    public function make_drop_table_ddl ($table_name, &$ddls) {
        global $trace;
        if ($trace) trace_entry();
        $ddl = "drop table if exists $this->schema_name.$table_name";
        $ddls[] = $ddl;
        if ($trace) trace_exit();
    }

    public function make_rename_table_ddl ($table_name, $new_table_name, &$ddls) {
        global $trace;
        if ($trace) trace_entry();
        $ddl = "rename table $this->schema_name.$table_name to $this->schema_name.$new_table_name";
        $ddls[] = $ddl;
        if ($trace) trace_exit();
    }

    public function make_export_table_sql ($table_name, $params, $columns, $options, $filename, &$sqls) {
        global $trace;
        if ($trace) trace_entry();
        $sql = $this->_make_select_sql($table_name, $params, $columns, $options);
        $sql .= "into outfile '$filename' fields terminated by ',' optionally enclosed by '\"'";
        $sqls[] = $sql;
        if ($trace) trace_exit();
    }

    public function make_import_table_sql ($table_name, $columns, $options, $filename, &$sqls) {
        global $trace;
        if ($trace) trace_entry();
        $schema_name = $this->schema_name;

        $sql = "load data local infile '$filename' into table $schema_name.$table_name " .
               "columns terminated by ',' optionally enclosed by '\"' escaped by '\\\\' lines terminated by '\\n'";
        $sqls[] = $sql;
        if ($trace) trace_exit();
    }

#    protected function app_type_to_native_type ($app_data_type, $max_length = null, $number_of_decimals = null, $column_name = null) {
#        global $trace;
#        if ($trace) trace_entry();
#
#        $app_data_type = isset($app_data_type) ? strtolower($app_data_type) : 'string';
#        $matches = array();
#        if (preg_match('/^([a-zA-Z]+)\\(([0-9]+),?([0-9]*)\\)/', $app_data_type, $matches)) {
#            $app_data_type = $matches[1];
#            if ($matches[2]) $max_length  = $matches[2] + 0;
#            if ($matches[3]) $number_of_decimals = $matches[3] + 0;
#        }
#
#        if ($app_data_type == 'date' ||
#            $app_data_type == 'datetime' ||
#            $app_data_type == 'time') {
#            $native_data_type = $app_data_type;
#        }
#        elseif (preg_match('/int|signed/', $app_data_type)) {
#            $native_data_type = 'integer';
#        }
#        elseif (preg_match('/float/', $app_data_type)) {
#            $native_data_type = 'float';
#        }
#        elseif (preg_match('/text/', $app_data_type)) {
#            $native_data_type = 'text';
#        }
#        elseif (preg_match('/blob/', $app_data_type)) {
#            $native_data_type = 'blob';
#        }
#        elseif (preg_match('/string/', $app_data_type)) {
#            if (!isset($max_length)) $max_length = 255;
#            $native_data_type = "varchar($max_length)";
#        }
#        else {
#            $native_data_type = 'blob';
#        }
#        if ($trace) trace_exit($native_data_type);
#        return $native_data_type;
#    }

    protected function _make_limit_clause ($table, $options) {
        global $trace;
        if ($trace) trace_entry();
        $limit_clause = '';
        if (isset($options['limit']) && $options['limit']) {
            $limit_clause = "\nlimit " . $options['limit'];
            if (isset($options['offset']) && $options['offset'] > 0) {
                $limit_clause .= " offset " . $options['offset'];
            }
        }
        if ($trace) trace_exit($limit_clause);
        return($limit_clause);
    }

    public function date_literal ($year, $month, $day) {
        return("'$year-$month-$day'");
    }

    public function datetime_literal ($year, $month, $day, $hours = '00', $min = '00', $sec = '00') {
        return("'$year-$month-$day $hours:$min:$sec'");
    }

    public function relative_date_literal ($days, $base_date = null) {
        if ($base_date == null) return("date_add(curdate(),INTERVAL $days DAY)");
        else                    return("date_add('$base_date',INTERVAL $days DAY)");
    }

    public function relative_datetime_literal ($days, $hours = '00', $min = '00', $sec = '00', $base_datetime = null) {
        if ($days == intval($days)) {
            if ($base_datetime == null) return("date_add(now(),INTERVAL '$days $hours:$min:$sec' DAY_SECOND)");
            else                        return("date_add('$base_datetime',INTERVAL '$days $hours:$min:$sec' DAY_SECOND)");
        }
        else {
            if ($base_datetime == null) return("date_add(now(),INTERVAL $days DAY)");
            else                        return("date_add('$base_datetime',INTERVAL $days DAY)");
        }
    }
}

